#!/usr/bin/env python
# -*- coding: utf-8 -*-
"""
============================================================================
    Desc: 过滤出监控日报中cpu、内存资源使用大于70%的服务器
    Author: zhoubangjun
    Date: 2024/6/6 
=============================================================================
"""
import os
import logging
import sys

import pandas as pd
from openpyxl import load_workbook
import datetime

logging.basicConfig(level=logging.INFO)
base_dir = os.path.dirname(os.path.abspath(__file__))

server_inspetction_archive_dir = os.path.join(base_dir, '跨模型监控报表日报')
server_usage_gt_70_archive_dir = os.path.join(base_dir, 'archive')
os.makedirs(server_inspetction_archive_dir, exist_ok=True)
os.makedirs(server_usage_gt_70_archive_dir, exist_ok=True)


def get_latest_file(archive_dir):
    files = os.listdir(archive_dir)
    files.sort(key=lambda x: os.path.getmtime(os.path.join(archive_dir, x)))
    return files[-1]


def filter_usage_gt_70():
    today = datetime.datetime.now().date()
    previous_day = today - datetime.timedelta(days=1)
    latest_server_report_file_name = get_latest_file(server_inspetction_archive_dir)
    server_report_path = f'{server_inspetction_archive_dir}/{latest_server_report_file_name}'

    logging.info(f'latest_server_report_file_name: {latest_server_report_file_name}')
    server_usage_gt_70_report_path = f"{server_usage_gt_70_archive_dir}/{previous_day.strftime('%Y.%m.%d')}-监控报表日报-cpu或内存高于70%的服务器.xlsx"

    # engine='xlrd': supports old-style Excel files (.xls). which python > 3.8 is not supported
    if f'{sys.version_info.major}.{sys.version_info.minor}' >= '3.8':
        df_server_report = pd.read_excel(server_report_path, engine='openpyxl', skiprows=range(3))
    else:
        df_server_report = pd.read_excel(server_report_path, engine='xlrd', skiprows=range(3))

    filtered_df = df_server_report[((df_server_report['最大值'].str.rstrip('%').astype(float) / 100) > 0.7) |
                      ((df_server_report['平均值'].str.rstrip('%').astype(float) / 100) > 0.7)]

    filtered_df_copy = filtered_df.copy()
    columns_to_delete = ['上限值', '最大值利用率', '视图']
    try:
        filtered_df_copy.drop(columns_to_delete, axis=1, inplace=True)
    except Exception as e:
        logging.error(f'删除列失败, e: {e}')

    with pd.ExcelWriter(server_usage_gt_70_report_path, engine='openpyxl', mode='w') as writer:
        filtered_df_copy.to_excel(writer, sheet_name='Sheet', index=False)

    logging.info(f'数据处理完成,文件位置: {server_usage_gt_70_report_path}')


if __name__ == '__main__':
    filter_usage_gt_70()
